package server;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import GUI.ResultDisplay;

import logic.Book;
import logic.Member;
import logic.Message;
import logic.SystemMesseges;
import ocsf.server.ConnectionToClient;


public class MemberController {

	private DB_Manager DB;
	private OBL_Server parent;
	
	public MemberController(OBL_Server parent,DB_Manager db) {
		this.parent = parent;
		this.DB = db;
	}
	
	/**
	 * This function query the DB for users for the requested status
	 * @param status - the requested status to look for
	 * @return number of students for this status, -1 for failure
	 */
	public int GetNumberOfStudentsForStauts(int status){
		String query = "SELECT COUNT(UID) AS VAL FROM users WHERE STATUS + " + status + ";";
		try{
			ResultSet rs = DB.Query(query);

			rs.next();
			int value = rs.getInt("VAL");
			rs.close();
			return value;
		}catch(SQLException sql){
			parent.Log("[GetNumberOfStudentsForStauts] SQLException. " + sql.getMessage());
		}catch(NullPointerException nul){
			parent.Log("[GetNumberOfStudentsForStauts] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[GetNumberOfStudentsForStauts] ClassCastException. " + cl.getMessage());

		}
		return -1;
		
	}
	
	
	/**
	 * This function sets the status of the selected user, with the requested value
	 * @param received - the Member to affect
	 */
	public void SetMemberCardStatus(Message received){
		try{
			ArrayList<Object> data = received.GetMessageData();
			int status = (Integer)data.get(1);
			int UID = (Integer)data.get(0);
			String query = "UPDATE users SET STATUS = " + status + " WHERE UID = " + UID;
			DB.Update(query);	
		}catch(NullPointerException nul){
			parent.Log("[SetMemberCardStatus] NullPointerException. " + nul.getMessage());
		}
		
	}
	
	
	/**
	 * This function will search the DB for member by the requested fields
	 * The Result will be sent as a Message to the client
	 * @param received - the Message object containing the fields and their requested values
	 * @param client - the client which requested the search
	 */
	public void SearchMember(Message received, ConnectionToClient client){
		
		ResultSet rs = QueryMember(received.GetMessageIndexes(), received.GetMessageData());
		
		ArrayList<Member> memberList = SeralizeResultSet(rs);
		
		ArrayList<String> index = new ArrayList<String>();
		index.add("INDICATOR");
		index.add("RESULTSET");
		index.add("DIRECTOR");
		ArrayList<Object> data = new ArrayList<Object>();
		data.add(ResultDisplay.MEMBER);
		data.add(memberList);
		data.add(received.GetMessageData().get(0));
		
		int action = Message.SEARCH_MEMBER_RESPONSE;
		
		Message toSend = new Message(index, data, action);
		
		try{
			client.sendToClient(toSend);
		}catch(IOException e){
			parent.Log("[SearchMember] IOException. " + e.getMessage());
		}catch(NullPointerException nul){
			parent.Log("[SearchMember] NullPointerException. " + nul.getMessage());
		}	
	}
	
	
	
	/**
	 * This function will search the DB for member by the requested fields
	 * The Result will be sent as a Message to the client
	 * @param received - the Message object containing the fields and their requested values
	 * @param client - the client which requested the search
	 */
	public void SearchWorker(Message received, ConnectionToClient client){
		
		ResultSet rs = QueryWorker(received.GetMessageIndexes(), received.GetMessageData());
		
		ArrayList<Member> memberList = SeralizeResultSet(rs);
		
		ArrayList<String> index = new ArrayList<String>();
		index.add("INDICATOR");
		index.add("RESULTSET");
		index.add("DIRECTOR");
		ArrayList<Object> data = new ArrayList<Object>();
		data.add(ResultDisplay.MEMBER);
		data.add(memberList);
		data.add(received.GetMessageData().get(0));
		
		int action = Message.SEARCH_WORKER_RESPONSE;
		
		Message toSend = new Message(index, data, action);
		
		try{
			client.sendToClient(toSend);
		}catch(IOException e){
			parent.Log("[SearchWorker] IOException. " + e.getMessage());
		}catch(NullPointerException nul){
			parent.Log("[SearchWorker] NullPointerException. " + nul.getMessage());
		}	
	}
	
	
	//TODO move this to bookcontroller
	/**
	 * This Function update the member information in the DB
	 * @param member - the Message object containing the member's information
	 * @param client - Client which created the book
	 * @return 1 for valid, 0 for error
	 */
	public int UpdateBook(Message member,ConnectionToClient client){
		
		ArrayList<Object> data = member.GetMessageData();
		ArrayList<String> index = member.GetMessageIndexes();
			
		try{
			//Book does not exist, ok to add!
			String updateQuery = "UPDATE users SET " + (String)index.get(1) + " = '" + (String)data.get(1) + "'";
			for (int i=2; i< data.size(); i++){
				updateQuery += ", " + (String)index.get(i) + " = '" + (String)data.get(i) + "'";
			}
			updateQuery += " WHERE " + (String)index.get(0) + " = '" + (Integer)data.get(0) + "'";
			return DB.Update(updateQuery);	
		}catch(NullPointerException nul){
			parent.Log("[UpdateBook] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[UpdateBook] ClassCastException. " + cl.getMessage());
		}	
		return 0;
		
	}
	
	
	
	/**
	 * This function query the DB for entered UID and validates the membercard status
	 * @param uid - the UID to query for
	 * @return true if membercard is valid, false otherwise
	 */
	public boolean getMemberCardStatus(String uid){
		String query = "SELECT STATUS FROM users WHERE UID = " + uid + " AND PRIVILIGE = 1000";
		
		ResultSet rs = DB.Query(query);
		try {
			while(rs.next()){
				if (rs.getInt("STATUS") == Member.VALID_MEMBERCARD) return true;
				else return false;
			}
			
		}catch (SQLException e) {
			parent.Log("[getMemberCardStatus] SQLException. " + e.getMessage());			
		}catch(ClassCastException cl){
			parent.Log("[getMemberCardStatus] ClassCastException. " + cl.getMessage());
		}
		
		return false;
	}
	
	
	
	
	
	/**
	 * This function query the DB for entered UID and validates the member name
	 * @param uid - the UID to query for
	 * @return member name
	 */
	public String getMemberName(String uid){
		String query = "SELECT NAME FROM users WHERE UID = " + uid + " AND PRIVILIGE = 1000";
		
		try {
			ResultSet rs = DB.Query(query);

			rs.next();
			String name = rs.getString("NAME");
			rs.close();
			return name;
			
		}catch (SQLException e) {
			parent.Log("[getMemberName] SQLException. " + e.getMessage());			
		}catch(NullPointerException nul){
			parent.Log("[getMemberName] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[getMemberName] ClassCastException. " + cl.getMessage());
		}
		
		return null;
	}
	
	
	
	
	/**
	 * This Function query the DB for the entered UID and validate that such member exists
	 * @param uid - the UID to look for
	 * @return true if exists, false otherwise
	 */
	public boolean ValidateMember(String uid){
		String query = "SELECT * FROM users WHERE UID = " + uid + " AND PRIVILIGE = 1000";
		
		int count = 0;
		try {
			ResultSet rs = DB.Query(query);

			while(rs.next()){
				count++;
			}
			
			if (count>0) return true;
		}catch (SQLException e) {
			parent.Log("[ValidateMember] SQLException. " + e.getMessage());			
		}catch(NullPointerException nul){
			parent.Log("[ValidateMember] NullPointerException. " + nul.getMessage());
		}
		
		return false;
	}
	
	
	
	/**
	 * This Function convert a ResultSet into ArrayList<Member> for sending as a Message
	 * (ResultSet is not seralizeable)
	 * @param rs - the ResultSet to conver
	 * @return ArrayList<Member> which conatins all the members in the ResultSet
	 */
	public ArrayList<Member> SeralizeResultSet(ResultSet rs){
		
		ArrayList<Member> memberList = new ArrayList<Member>();
		
		try{
			while(rs.next()){	
				//Member(int UID,String name, String dob, String phone, String mail, String address,int status)
				Member member = new Member(rs.getInt("UID"), rs.getString("NAME"), rs.getString("DOB"), 
						rs.getString("PHONE"), rs.getString("MAIL"), rs.getString("ADDRESS"),rs.getInt("STATUS"));
				
				memberList.add(member);
			}
			
			return memberList;
		}catch (SQLException e) {
			parent.Log("[SeralizeResultSet] SQLException. " + e.getMessage());			
		}catch(ClassCastException cl){
			parent.Log("[SeralizeResultSet] ClassCastException. " + cl.getMessage());
		}
		return null;
	}
	
	
	/**
	 * This function is meant to query the actual DB for the fields and return the ResultSet
	 * @param fields - the fields to query by
	 * @param data - the data to query by
	 * @return ResultSet which contains all qualified tuples
	 */
	public ResultSet QueryMember(ArrayList<String> fields,ArrayList<Object> data){
		
		try{
			String query = "SELECT * FROM users WHERE PRIVILIGE = 1000";
			if (fields.size() > 1) {
				String field;
				for(int i=1; i < fields.size(); i++){
					field = (String)fields.get(i);
					if ((field.equals("DOB")) || (field.equals("MAIL"))) query += " AND " + field + " = '" + (String)data.get(i) + "'";
					else query += " AND " + field + " = '" + (String)data.get(i) + "'";
				}
				query += ";";
			}
			
			return DB.Query(query);
			
		}catch(NullPointerException nul){
			parent.Log("[QueryMember] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[QueryMember] ClassCastException. " + cl.getMessage());
		}
		return null;
	}
	
	
	/**
	 * This function is meant to query the actual DB for the fields and return the ResultSet
	 * @param fields - the fields to query by
	 * @param data - the data to query by
	 * @return ResultSet which contains all qualified tuples
	 */
	public ResultSet QueryWorker(ArrayList<String> fields,ArrayList<Object> data){
		
		try{
			String query = "SELECT * FROM users WHERE PRIVILIGE = 100";
			if (fields.size() > 1) {
				String field;
				for(int i=1; i < fields.size(); i++){
					field = (String)fields.get(i);
					if ((field.equals("DOB")) || (field.equals("MAIL"))) query += " AND " + field + " = '" + (String)data.get(i) + "'";
					else query += " AND " + field + " = '" + (String)data.get(i) + "'";
				}
				query += ";";
			}
			
			return DB.Query(query);
		}catch(NullPointerException nul){
			parent.Log("[QueryWorker] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[QueryWorker] ClassCastException. " + cl.getMessage());
		}
		return null;
	}
	
	
	
	
	/**
	 * Create a new member based on the information on the message attached
	 * @param member - the Message object that contains all the member's information
	 * @return 1 for success, 0 otherwise
	 */
	public int CreateMember(Message member,ConnectionToClient client){
		
		ArrayList<Object> data = member.GetMessageData();
		
		String UID = (String)data.get(0);
		
		try{
			
			ResultSet rs = DB.Query("SELECT * FROM users WHERE UID = " + UID + ";");
			
			int actionOUT = Message.CREATE_MEMBER_RESPONSE;
			ArrayList<String> indexOUT = new ArrayList<String>();
			ArrayList<Object> dataOUT = new ArrayList<Object>();
			
			int count = 0;
		
			while(rs.next()){
				count++;
			}
			
			if (count > 0){
				indexOUT.add("ERROR");
				dataOUT.add("Member already exists!");
				Message toSend = new Message(indexOUT,dataOUT,actionOUT);
				try{
					client.sendToClient(toSend);	
				}catch(IOException e){
					parent.Log("[CreateMember] IOException. " + e.getMessage());
				}
				return 0;
				
			}

			//member does not exist, ok to add!
			DB.Update("INSERT INTO USERS " +
					"(UID, PASSWORD, NAME, DOB, PRIVILIGE, PHONE, LOGGED_IN, MAIL, ADDRESS,STATUS) VALUES" +
					"('" + UID + "','" + (String)data.get(1) + "','" + (String)data.get(2) + "','" + (String)data.get(3) + 
					"','1000','" + (String)data.get(4) + "','0','" + (String)data.get(5) + "','" + (String)data.get(6) +
					"','" + Member.VALID_MEMBERCARD + "')");
			indexOUT.add("OK");
			dataOUT.add("Member Created");
			Message toSend = new Message(indexOUT,dataOUT,actionOUT);
			
			try{
				client.sendToClient(toSend);	
			}catch(IOException e){
				parent.Log("[CreateMember] IOException. " + e.getMessage());
			}
			return 1;
			
		}catch(SQLException e){
			parent.Log("[CreateMember] SQLException. " + e.getMessage());
		}catch(NullPointerException nul){
			parent.Log("[CreateMember] NullPointerException. " + nul.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[CreateMember] ClassCastException. " + cl.getMessage());
		}
		
		return 1;
	}
	
	
	/**
	 * This function will simluate a login for a system user.
	 * GENERIC user - which will not update nothing in the DB
	 * only query global messages for system user
	 * @param client
	 */
	public void LoginSystemUser(ConnectionToClient client){
		MessageController messageController = new MessageController(parent, DB);
		
		ArrayList<SystemMesseges> publicMessages = messageController.GetSystem_Message(Member.SYSTEM_USER_PRIVILIGE,null);
		
		ArrayList<String> index = new ArrayList<String>();
		ArrayList<Object> data = new ArrayList<Object>();
		
		index.add("Privelege");						//code privelege message
		data.add(Member.SYSTEM_USER_PRIVILIGE);		//code the privelge code
		index.add("UserName");						//code the user name message 
		data.add("GUEST");							//code the user name
		
		index.add("PUBLICMESSAGES");
		data.add(publicMessages);
		
		Message msg = new Message(index, data, Message.LOGIN_RESPONSE);
		
		try {
			client.sendToClient(msg);
			parent.gui.DisplayLoggedUsers();
		} catch (IOException e) {
			parent.Log("[LoginSystemUser] IOException. " + e.getMessage());
		}
	}
	
	
	
	/** Login - user has clicked login button - wants to connect to the server
	 * @param user - entered username
	 * @param pass - entered password
	 * @param client - client which sent the login requst
	 */
	public void Login(String user, String pass,ConnectionToClient client){
		MessageController messageController = new MessageController(parent, DB);
		int count = 0;
		
		try{
			
			ResultSet rs = DB.Query("SELECT * FROM Users WHERE UID = " + user + ";");
		
			ArrayList<String> index = new ArrayList<String>();
			ArrayList<Object> data = new ArrayList<Object>();
			int action = Message.LOGIN_RESPONSE;

	 		while(rs.next()) {
	 			count++;
	 			if (rs.getString("password").equals(pass)){
	 				if (rs.getInt("LOGGED_IN") == 0){
						 	 					
	 					DB.Update("UPDATE users SET LOGGED_IN = 1 WHERE UID = " + user);
						
						index.add("Privelege");				//code privelege message
						data.add(rs.getInt("PRIVILIGE"));	//code the privelge code
						index.add("UserName");				//code the user name message 
						data.add(rs.getString("NAME"));		//code the user name
						
						ArrayList<SystemMesseges> publicMessages = messageController.GetSystem_Message(rs.getInt("PRIVILIGE"),null);
						index.add("PUBLICMESSAGES");
						data.add(publicMessages);
						
						ArrayList<SystemMesseges> privateMessages = messageController.GetSystem_Message(Integer.parseInt(user),null);
						index.add("PRIVATEMESSAGES");
						data.add(privateMessages);
						
						Message msg = new Message(index, data, action);
						
						try {
							client.sendToClient(msg);
							client.setInfo("UID", user);
						} catch (IOException e) {
							parent.Log("[Login] IOException. " + e.getMessage());
						}
					 }else{
							index.add("Privelege");				//code privelege message
							data.add(-1);						//code the privelge code for error
							index.add("LoginResult");				//code privelege message
							data.add("User Already Logged");	//code the privelge code
							
							Message msg = new Message(index, data, action);
							
							try {
								client.sendToClient(msg);
								parent.gui.DisplayLoggedUsers();
							} catch (IOException e) {
								parent.Log("[Login] IOException. " + e.getMessage());

							}
					 }
				 }else{ //Wrong password - end of password IF
					 
						index.add("Privelege");				//code privelege message
						data.add(-1);						//code the privelge code for error
						index.add("LoginResult");				//code privelege message
						data.add("Bad User/Login");	//code the privelge code
						
						Message msg = new Message(index, data, action);
						
						try {
							client.sendToClient(msg);
							parent.gui.DisplayLoggedUsers();
						} catch (IOException e) {
							parent.Log("[Login] IOException. " + e.getMessage());
						}
				 }	
	 		}
	 		
	 		if (count == 0){
				index.add("Privelege");				//code privelege message
				data.add(-1);						//code the privelge code for error
				index.add("LoginResult");				//code privelege message
				data.add("Bad User/Login");	//code the privelge code
				
				Message msg = new Message(index, data, action);
				
				try {
					client.sendToClient(msg);
					parent.gui.DisplayLoggedUsers();
				} catch (IOException e) {
					parent.Log("[Login] IOException. " + e.getMessage());

				}
	 		}
			rs.close();
		}catch (SQLException e) {
			parent.Log("[Login] SQLException. " + e.getMessage());
		}catch (NullPointerException e) {
			parent.Log("[Login] NullPointerException. " + e.getMessage());
		}catch(ClassCastException cl){
			parent.Log("[Login] ClassCastException. " + cl.getMessage());
		}
	}
	
	
	/** Login - user has clicked logoff button - wants to disconnect from the server
	 * @param user - UID to logoff
	 */
	public void Logoff(String user){		
		DB.Update("UPDATE users SET LOGGED_IN = 0 WHERE UID = " + user);
	}

}
